<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/8/27 0027
 * Time: 13:39
 */
namespace app\admin\model;
class Excel
{
    /**excel导出
     * @param $data
     * @param array $field
     * @param string $title
     */
    static public function dataToExcel($data,array $field,$title='标题'){
        //导出表格
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        // 设置水平垂直居中
        $objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //设置表头
//  合并
        $az = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $str = $az[count($field)-1];
        $objExcel->getActiveSheet()->mergeCells('A1:'.$str.'1');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle($title);//设置excel的标题
        $objActSheet->setCellValue('A1',$title);
        $x = 0;
        foreach ($field as $value){
            $objActSheet->setCellValue($az[$x].'2',$value);
            $x++;
        }
        $baseRow = 3; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        foreach ( $data as $r => $d ) {
            $i = $baseRow + $r;
            $x=0;
            foreach ($field as $k=>$values){
                $objExcel->getActiveSheet()->setCellValue($az[$x].$i,$d[$k]);
                $x++;
            }
        }
        $objExcel->setActiveSheetIndex(0);
        //4、输出
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        $time=date('YmdHis');
        header("Content-Disposition: attachment;filename=用户统计$time.xls");
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    /**导入excel
     * @param $filename
     * @param bool $xlsx
     * @return array
     */
    static public function excelToData($filename,$xlsx=false){
//        $PHPExcel = new \PHPExcel();
        //判断是xsl还是xlsx
        if($xlsx){
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }else{
            $PHPReader = new \PHPExcel_Reader_Excel5();
        }
        //分会所有的数据
        return $PHPReader->load($filename)->getSheet(0)->toArray();
    }
}